--数据量520w+/天
CREATE TABLE jms_dm.dm_end_network_taking_notout_over22d_cnt_dt (
    virt_code varchar(30) NULL COMMENT "虚拟代理区code",
    agent_code varchar(30) NULL COMMENT "代理区code",
    fran_code varchar(30) NULL COMMENT "加盟商code",
    ordersource_name varchar(30) NULL COMMENT "订单来源",
    in_network_code varchar(30) NULL COMMENT "最早入库网点code",
    in_network_name varchar(120) NULL COMMENT "最早入库网点name",
    store_code varchar(60) NULL COMMENT "最早入库门店",
    business_id varchar(30) NULL COMMENT "品牌id",
    business_name varchar(120) NULL COMMENT "品牌名称",
    store_name varchar(120) NULL COMMENT "门店名称",
    store_address varchar(120) NULL COMMENT "门店地址",
    build_type int(11) NULL COMMENT "建设类型 1自建2合作 null",
    source_type varchar(30) NULL COMMENT "末端类型：机柜、驿站",
    agent_name varchar(60) NULL COMMENT "代理区name",
    fran_name varchar(120) NULL COMMENT "加盟商name",
    deliver_cnt int(11) NULL COMMENT "派件量",
    in_cnt int(11) NULL COMMENT "入库量",
    notout_21d_cnt int(11) NULL COMMENT "揽收超21日出库量",
    notout_22d_cnt int(11) NULL COMMENT "揽收超22日出库量",
    date_time date NULL COMMENT "查询时间",
    cnt_type tinyint(4) NULL COMMENT "1入库日期统计2揽收日期统计",
    virt_name varchar(50) NULL COMMENT "虚拟代理区name"
) ENGINE=OLAP 
DUPLICATE KEY(virt_code, agent_code, fran_code, ordersource_name)
COMMENT "末端_揽收超22日未出库_汇总"
PARTITION BY RANGE(date_time)
(START ("2023-11-01") END ("2023-12-30") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(in_network_code) BUCKETS 6
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-186",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "6",
"in_memory" = "false",
"storage_format" = "V2",
"enable_persistent_index" = "false"
);